﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PBOffice.Models;
using System.Data;
using System.Data.SqlClient;

namespace PBOffice.DAL
{
    public class PosService
    {
        public List<Position> GetAllPos()
        {
            List<Position> list = new List<Position>();
            string sql = "select [PosId],[PosName],[DeptName] from Position p,Department d  where p.DeptId=d.DeptId ";
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Position pos = new Position();
                    Department dept = new Department();
                    // Branch ba = new Branch();
                    pos.PosId = Convert.ToInt32(dr["PosId"]);
                    pos.PosName = dr["PosName"].ToString();
                    //dept.DeptId = Convert.ToInt32(dr["DeptId"]);
                    dept.DeptName = dr["DeptName"].ToString();
                    //ba.BranchId = Convert.ToInt32(dr["BranchId"]);
                    //ba.BranchName = dr["BranchName"].ToString();
                    //ba.BranchShortName = dr["BranchShortName"].ToString();
                    // dept.Branch = ba;
                    pos.Department = dept;
                    list.Add(pos);
                }
            }
            return list;
        }
        public List<Position> GetPosByDept(int depId)
        {
            List<Position> list = new List<Position>();
            string sql = string.Format("select * from Position where DeptId={0}", depId);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Position pos = new Position();
                    pos.PosId = (int)dr["PosId"];
                    pos.PosName = dr["PosName"].ToString();
                    list.Add(pos);
                }
            }
            return list;
        }
        public List<Position> GetPosByDept(string dep)
        {
            List<Position> list = new List<Position>();
            string sql = string.Format("select d.DeptId,d.DeptName,p.PosId,p.PosName from Position p,Department d where p.DeptId=d.DeptId and d.DeptName='{0}'", dep);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Position pos = new Position();
                    pos.PosId = (int)dr["PosId"];
                    pos.PosName = dr["PosName"].ToString();
                    Department dept = new Department();
                    dept.DeptName = dr["DeptName"].ToString();
                    dept.DeptId = (int)dr["DeptId"];
                    pos.Department = dept;
                    list.Add(pos);
                }
            }
            return list;
        }
        public List<Position> GetPosByPosName(string name)
        {
            List<Position> list = new List<Position>();
            string sql = string.Format("select * from Position where PosName={0}", name);
            using (IDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Position pos = new Position();
                    pos.PosId = (int)dr["PosId"];
                    pos.PosName = dr["PosName"].ToString();
                    list.Add(pos);
                }
            }
            return list;
        }
        public void AddPosition(Position pos)
        {
            string sql = "insert [Position] (PosName,DeptId) values (@PosName,@DeptId)";
            SqlParameter[] spa = { new SqlParameter("@PosName",pos.PosName),
                                     new SqlParameter("@DeptId",pos.Department.DeptId)
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);

        }
        public void UpdatePos(Position pos)
        {
            string sql = "update [Position]  set PosName=@PosName,DeptId=@DeptId where PosId=@PosId";
            SqlParameter[] spa = { new SqlParameter("@PosId",pos.PosId),
                                     new SqlParameter("@PosName",pos.PosName),
                                     new SqlParameter("@DeptId",pos.Department.DeptId)
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public void DeletePos(int id)
        {
            string sql = string.Format("delete Position where PosId={0}", id);
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql);
        }
        public int GetPosIdByName(string name)
        {
            int posId = 0;
            string sql = string.Format("select PosId from Position where PosName={0}'", name);
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    posId = (int)dr["PosId"];
                }
            }
            return posId;
        }
    }
}
